Relational database systems store data in tables organised by columns and rows. The tables typically are linked together by “relationships” that simplify the storage of the data and make complex queries against the database more efficient. SQL is a standardised language for creating and operating on relational databases. An example of a SQL program product is “TERADATA”(RTM) owned by NCR Corporation.
Rewriting SQL queries is particularly useful for improving their efficiency when the views or Derived Tables (DTs) involved in the queries are “folded”. “Folding” a view or DT query means the query is rewritten without reference to the view or DT. However, the folding of some queries can produce incorrect results. For example, the folding of a query having outer joins (full, right or left) and where at least one of the views or the DTs used in the query has a null sensitive expression (NSE), can result in the NSE being incorrectly replicated. NSEs are expressions containing constants, case expressions, zeroifnull expressions or the SQL coalesce function. In prior art methods, expressions (including NSEs) are executed, or instantiated, during the last step of the query. Therefore, the NSE should be null if it belongs to the un-matching rows of the view or DT, however there is no way to determine this after the joins have been made.
This problem is illustrated in the following Example 1, where the NSE is a constant expression: